﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using com.hyrentals.data;
using com.hyrentals.common;
using ServiceStack.Text;

namespace com.hyrentals.dbaccess
{
    public class NoticeDBAccess : DBAccessBase
    {
        public NoticeDBAccess()
            : base(ConfigurationManager.ConnectionStrings["APPContext"].ConnectionString) { }

        public NoticeDBAccess(string connectionString) :
            base(connectionString) { }

        public IEnumerable<Notice> Customer_GetNoticesFromInbox_Paging(User user, UserTypeEnum userType, int page, int pageSize)
        {
            int start = (page - 1) * pageSize;
            int count = pageSize;
            return Customer_GetNoticesFromInbox(user, userType, start, count);
        }

        public IEnumerable<Notice> Customer_GetNoticesFromInbox(User user, UserTypeEnum userType, int start, int count)
        {
            List<Notice> notices = new List<Notice>();
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_ID, DBParamNameConstant.NOTICE_RECEIVER_ID_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_ID_SIZE, user.Basic.UserId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)userType),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME, DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME_SIZE, user.Detail.RegistrationDate),
                                            DbHelper.MakeInParam(DBParamNameConstant.START, DBParamNameConstant.START_DB_TYPE, DBParamNameConstant.START_SIZE, start),
                                            DbHelper.MakeInParam(DBParamNameConstant.COUNT, DBParamNameConstant.COUNT_DB_TYPE, DBParamNameConstant.COUNT_SIZE, count)
                                        };
                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[Customer_GetNoticesFromInbox]", parms);
                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable noticeTable = ds.Tables[0];
                    foreach (DataRow dataRow in noticeTable.Rows)
                    {
                        DateTime? postTime, expireTime;
                        string senderId = null;

                        if (dataRow[DBColumnNameConstant.NOTICE_POST_TIME] is DBNull)
                            postTime = null;
                        else
                            postTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_POST_TIME];

                        if (dataRow[DBColumnNameConstant.NOTICE_EXPIRE_TIME] is DBNull)
                            expireTime = null;
                        else
                            expireTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_EXPIRE_TIME];

                        if (!(dataRow[DBColumnNameConstant.NOTICE_SENDER_ID] is DBNull))
                            senderId = (string)dataRow[DBColumnNameConstant.NOTICE_SENDER_ID];


                        notices.Add(new Notice
                        {
                            NoticeId = Convert.ToInt32(dataRow[DBColumnNameConstant.NOTICE_ID]),
                            SenderId = senderId,
                            SenderType = (UserTypeEnum)Enum.Parse(typeof(UserTypeEnum), dataRow[DBColumnNameConstant.NOTICE_SENDER_TYPE].ToString()),
                            Subject = (string)dataRow[DBColumnNameConstant.NOTICE_SUBJECT],
                            Message = (string)dataRow[DBColumnNameConstant.NOTICE_MESSAGE],
                            Type = (NoticeType)Enum.Parse(typeof(NoticeType), dataRow[DBColumnNameConstant.NOTICE_TYPE].ToString()),
                            SendTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_SEND_TIME],
                            PostTime = postTime,
                            ExpireTime = expireTime,
                            SendToId = Convert.ToInt32(dataRow[DBColumnNameConstant.NOTICE_SENDTO_ID]),
                            ReceiveStatus = (NoticeStatus)Enum.Parse(typeof(NoticeStatus), dataRow[DBColumnNameConstant.NOTICE_RECEIVE_STATUS].ToString())
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "Customer_GetNoticesFromInbox",
                    UserId = user.Basic.UserId,
                    UserType = userType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return notices;
        }

        public Notice Customer_GetNoticeFromInboxById(int sendToId, string userId, UserTypeEnum userType)
        {
            Notice notice = null;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SENDTO_ID, DBParamNameConstant.NOTICE_SENDTO_ID_DB_TYPE, DBParamNameConstant.NOTICE_SENDTO_ID_SIZE, sendToId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_ID, DBParamNameConstant.NOTICE_RECEIVER_ID_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_ID_SIZE, userId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)userType)
                                        };
                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[Customer_GetNoticeFromInboxById]", parms);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dataRow = ds.Tables[0].Rows[0];

                    DateTime? postTime, expireTime;
                    string senderId = null;

                    if (dataRow[DBColumnNameConstant.NOTICE_POST_TIME] is DBNull)
                        postTime = null;
                    else
                        postTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_POST_TIME];

                    if (dataRow[DBColumnNameConstant.NOTICE_EXPIRE_TIME] is DBNull)
                        expireTime = null;
                    else
                        expireTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_EXPIRE_TIME];

                    if (!(dataRow[DBColumnNameConstant.NOTICE_SENDER_ID] is DBNull))
                        senderId = (string)dataRow[DBColumnNameConstant.NOTICE_SENDER_ID];

                    notice = new Notice
                    {
                        NoticeId = Convert.ToInt32(dataRow[DBColumnNameConstant.NOTICE_ID]),
                        SenderId = senderId,
                        SenderType = (UserTypeEnum)Enum.Parse(typeof(UserTypeEnum), dataRow[DBColumnNameConstant.NOTICE_SENDER_TYPE].ToString()),
                        Subject = (string)dataRow[DBColumnNameConstant.NOTICE_SUBJECT],
                        Message = (string)dataRow[DBColumnNameConstant.NOTICE_MESSAGE],
                        Type = (NoticeType)Enum.Parse(typeof(NoticeType), dataRow[DBColumnNameConstant.NOTICE_TYPE].ToString()),
                        SendTime = (DateTime)dataRow[DBColumnNameConstant.NOTICE_SEND_TIME],
                        PostTime = postTime,
                        ExpireTime = expireTime,
                        SendToId = Convert.ToInt32(dataRow[DBColumnNameConstant.NOTICE_SENDTO_ID]),
                        ReceiveStatus = (NoticeStatus)Enum.Parse(typeof(NoticeStatus), dataRow[DBColumnNameConstant.NOTICE_RECEIVE_STATUS].ToString())
                    };

                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "Customer_GetNoticeFromInboxById",
                    UserId = userId,
                    UserType = userType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return notice;
        }

        public bool Customer_GetNoticeCountFromInbox(User user, UserTypeEnum userType, out int inboxCount, out int inboxUnreadCount, out int globalUnreadCount)
        {
            bool result = true;
            inboxCount = inboxUnreadCount = globalUnreadCount = 0;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_ID, DBParamNameConstant.NOTICE_RECEIVER_ID_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_ID_SIZE, user.Basic.UserId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)userType),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME, DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_REGISTER_TIME_SIZE, user.Detail.RegistrationDate),
                                            DbHelper.MakeOutParam("@inboxCount", SqlDbType.Int, 0),
                                            DbHelper.MakeOutParam("@inboxUnreadCount", SqlDbType.Int, 0),
                                            DbHelper.MakeOutParam("@globalUnreadCount", SqlDbType.Int, 0)
                                        };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[Customer_GetNoticeCountFromInbox]", parms);
                inboxCount = (int)parms[3].Value;
                inboxUnreadCount = (int)parms[4].Value;
                globalUnreadCount = (int)parms[5].Value;
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "Customer_GetNoticeCountFromInbox",
                    UserId = user.Basic.UserId,
                    UserType = userType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }

        public bool InsertNotice(string senderId, UserTypeEnum senderType, string subject, string message, string[] receiverIds, UserTypeEnum receiverType, NoticeType type, DateTime? postTime, DateTime? expireTime)
        {
            bool result = true;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SENDER_ID, DBParamNameConstant.NOTICE_SENDER_ID_DB_TYPE, DBParamNameConstant.NOTICE_SENDER_ID_SIZE, senderId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SENDER_TYPE, DBParamNameConstant.NOTICE_SENDER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_SENDER_TYPE_SIZE, (int)senderType),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVERS, DBParamNameConstant.NOTICE_RECEIVERS_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVERS_SIZE, receiverIds==null?null:string.Join(";", receiverIds)),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)receiverType),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SUBJECT, DBParamNameConstant.NOTICE_SUBJECT_DB_TYPE, DBParamNameConstant.NOTICE_SUBJECT_SIZE, subject),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_MESSAGE, DBParamNameConstant.NOTICE_MESSAGE_DB_TYPE, DBParamNameConstant.NOTICE_MESSAGE_SIZE, message),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SEND_STATUS, DBParamNameConstant.NOTICE_SEND_STATUS_DB_TYPE, DBParamNameConstant.NOTICE_SEND_STATUS_SIZE, (int)NoticeStatus.Normal),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVE_STATUS, DBParamNameConstant.NOTICE_RECEIVE_STATUS_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVE_STATUS_SIZE, (int)NoticeStatus.NoRead),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_TYPE, DBParamNameConstant.NOTICE_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_TYPE_SIZE, (int)type),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SEND_TIME, DBParamNameConstant.NOTICE_SEND_TIME_DB_TYPE, DBParamNameConstant.NOTICE_SEND_TIME_SIZE, DateTime.Now),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_POST_TIME, DBParamNameConstant.NOTICE_POST_TIME_DB_TYPE, DBParamNameConstant.NOTICE_POST_TIME_SIZE, postTime),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_EXPIRE_TIME, DBParamNameConstant.NOTICE_EXPIRE_TIME_DB_TYPE, DBParamNameConstant.NOTICE_EXPIRE_TIME_SIZE, expireTime)
                                        };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[InsertNotice]", parms);
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "InsertNotice",
                    SenderId = senderId,
                    SenderType = senderType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }

        public bool Customer_DeleteNoticeFromInbox(int[] sendToIds, string userId, UserTypeEnum userType)
        {
            bool result = true;

            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SENDTO_IDS, DBParamNameConstant.NOTICE_SENDTO_IDS_DB_TYPE, DBParamNameConstant.NOTICE_SENDTO_IDS_SIZE, string.Join(";",sendToIds)),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_ID, DBParamNameConstant.NOTICE_RECEIVER_ID_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_ID_SIZE, userId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)userType)
                                        };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[Customer_DeleteNoticeFromInbox]", parms);
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "Customer_DeleteNoticeFromInbox",
                    UserId = userId,
                    UserType = userType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }

        public bool Customer_UpdateNoticeSendToStatus(int sendToId, string userId, UserTypeEnum userType, NoticeStatus noticeStatus)
        {
            bool result = true;

            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_SENDTO_ID, DBParamNameConstant.NOTICE_SENDTO_ID_DB_TYPE, DBParamNameConstant.NOTICE_SENDTO_ID_SIZE, sendToId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_ID, DBParamNameConstant.NOTICE_RECEIVER_ID_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_ID_SIZE, userId),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVER_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVER_TYPE_SIZE, (int)userType),
                                            DbHelper.MakeInParam(DBParamNameConstant.NOTICE_RECEIVE_STATUS, DBParamNameConstant.NOTICE_RECEIVE_STATUS_DB_TYPE, DBParamNameConstant.NOTICE_RECEIVE_STATUS_SIZE, (int)noticeStatus)
                                        };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[Customer_UpdateNoticeSendToStatus]", parms);
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "Customer_UpdateNoticeSendToStatus",
                    UserId = userId,
                    UserType = userType,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }
    }
}
